<?php
defined('_JEXEC') or die('Restricted access'); // No direct access

jimport('joomla.application.component.model');

/**
 * Volunteer Model
 *
 * @package    GippslandSite
 * @subpackage Components
 */
class NormalModelNormal extends JModel
{
	var $searchQuery = null;
	
	/* 
	 * Gets a sql query and execute
	 * @return rows as object array 
	 */
	private function loadSearchresults($query)
	{
		$db =& JFactory::getDBO();
		$db->setQuery($query);
		$objList = $db->loadObjectList();
		
		if (sizeof($objList) == 0)
			return array();
		else
			return $objList;
	}
	
	/**
	 * 
	 * @return id, name and url as a object 
	 */
	function getLinks()
	{
		return $this->loadSearchresults('SELECT id,name,url FROM gps_links');
	}
	
	/**
	 * Gets list of all locations in db
	 * @return array of locations(id, name)
	 */
	
	function getLocations()
	{
		$db =& JFactory::getDBO();
		
		$query = 'SELECT id, location AS name FROM gps_location';
		$db->setQuery($query);
		$rows = $db->loadObjectList();
		
		return $rows;
	}
	
	/**
	 * Gets list of all timspans in db
	 * @return array of timspans(id, name)
	 */
	function getTimespans()
	{
		$db =& JFactory::getDBO();
		
		$query = 'SELECT id, timespan AS name FROM gps_timespan';
		$db->setQuery($query);
		$rows = $db->loadObjectList();
		
		return $rows;
	}
	
	/**
	 * Gets list of all industries in db
	 * @return array of industries(id, name)
	 */
	function getIndustries()
	{
		$db =& JFactory::getDBO();
		
		$query = 'SELECT id, industry AS name FROM gps_industry';
		$db->setQuery($query);
		$rows = $db->loadObjectList();
		
		return $rows;
	}
	
	function countFile($location_id, $timespan_id, $industry_id, $opt_keywords)
	{
		$sqlstr = $this->buildQuery($location_id, $timespan_id, $industry_id, $opt_keywords);
		
		return sizeof($this->loadSearchresults($sqlstr));
	}
	
	private function buildQuery($location_id, $timespan_id, $industry_id, $opt_keywords)
	{
		//creating sql string for location id,timespan id ,industry id
		$sqlstr = "SELECT * from gps_audiofile where 1=1";
		
		if ($location_id > 0 or $timespan_id > 0 or $industry_id > 0)
		{
			if ($location_id > 0)
			{
				$sqlstr .= ' && gps_audiofile.location_id=' . $location_id;
			}
			if ($timespan_id > 0)
			{
				$sqlstr .= ' && gps_audiofile.timespan_id=' . $timespan_id;
			}
			if ($industry_id > 0)
			{
				$sqlstr .= ' && gps_audiofile.industry_id=' . $industry_id;
			}
		}
		
		//echo $sqlstr;//test				
		
		$opt_keywords = strip_tags($opt_keywords);
		//$opt_keywords_arr=trim($opt_keywords_arr);
		//	 $type        = preg_replace('/[^A-Z0-9_\.-]/i', '', $type); 
		
		$opt_keywords_arr = explode(',', $opt_keywords);
		$flag             = 0;
		
		//creating sql string for optional keywords
		if (sizeof($opt_keywords_arr) > 0)
		{
			foreach ($opt_keywords_arr as $i)
			{
				$i = trim($i);
				if ($i != "")
				{
					$file_name = $i->id;
					if ($flag = 0)
					{
						$sqlstr .= "AND gps_audiofile.id IN (AND 1=1) ";
						$flag = 1;
					}
					$sqlstr .= " AND (select file_id from gps_filekeywords where gps_audiofile.id=gps_filekeywords.file_id and gps_filekeywords.keyword='" . $i . "')";
					//echo "inside foreach";//test						 	 
				}
			}
		}
		
		return $sqlstr;
	}
	
	function searchFile($location_id, $timespan_id, $industry_id, $opt_keywords)
	{
		$sqlstr = $this->buildQuery($location_id, $timespan_id, $industry_id, $opt_keywords);
		
		// execute sql query
		$db =& JFactory::getDBO();
		$db->setQuery($sqlstr);
		
		$this->searchQuery = $sqlstr;
		
		if ($rows = $db->loadObjectList())
		{
			return $rows;
		}
		else
			return array();
	}
	
	/**
	 * Items total
	 * @var integer
	 */
	var $_total = null;
	
	/**
	 * Pagination object
	 * @var object
	 */
	var $_pagination = null;
	
	function getPagination()
	{
		// Load the content if it doesn't already exist
		if (empty($this->_pagination))
		{
			jimport('joomla.html.pagination');
			$this->_pagination = new JPagination($this->getTotal(), $this->getState('limitstart'), $this->getState('limit'));
		}
		return $this->_pagination;
	}
	
	function getTotal()
	{
		// Load the content if it doesn't already exist
		if (empty($this->_total))
		{
			$query        = $this->_buildQuery();
			$this->_total = $this->_getListCount($query);
		}
		return $this->_total;
	}
	
	function getData()
	{
		// if data hasn't already been obtained, load it
		if (empty($this->_data))
		{
			$query       = $this->_buildQuery();
			$this->_data = $this->_getList($query, $this->getState('limitstart'), $this->getState('limit'));
		}
		
		return $this->_data;
	}
	
	function _buildQuery()
	{
		// return 'SELECT * FROM gps_audiofile';
		return $this->searchQuery;
	}
	
	function __construct()
	{
		parent::__construct();
		
		global $mainframe, $option;
		
		// Get pagination request variables
		$limit      = 3; //JRequest::getVar('limit', 0, '', 'int'); //10;//$mainframe->getUserStateFromRequest('global.list.limit', 'limit', $mainframe->getCfg('list_limit'), 'int');
		$limitstart = JRequest::getVar('limitstart', 0, '', 'int');
		
		// In case limit has been changed, adjust it
		$limitstart = ($limit != 0 ? (floor($limitstart / $limit) * $limit) : 0);
		
		$this->setState('limit', $limit);
		$this->setState('limitstart', $limitstart);
	}
	
	function getFile($fileId)
	{
		return $this->loadObject('SELECT * FROM gps_audiofile WHERE id = ' . $fileId);
	}
	
	private function loadObject($query)
	{
		$db =& JFactory::getDBO();
		$db->setQuery($query);
		return $db->loadObject();
	}
	
	private function loadResult($query)
	{
		$db =& JFactory::getDBO();
		$db->setQuery($query);
		return $db->loadResult();
	}
	
	function getFileKeywords($fileId)
	{
		$db =& JFactory::getDBO();
		$db->setQuery('SELECT keyword FROM gps_filekeywords WHERE file_id = ' . $fileId);
		return $db->loadResultArray();
	}
	
	function getLocationName($locId)
	{
		return $this->loadResult('SELECT location FROM gps_location WHERE id = ' . $locId);
	}
	
	function getTimespanName($timId)
	{
		return $this->loadResult('SELECT timespan FROM gps_timespan WHERE id = ' . $timId);
	}
	
	function getIndustryName($indId)
	{
		return $this->loadResult('SELECT industry FROM gps_industry WHERE id = ' . $indId);
	}
	
	function addFeedback($fileId, $name, $feedback)
	{
		$fileId = trim($fileId);
		if ($fileId != "")
		{
			return $this->executequery("INSERT INTO gps_filecomments (file_id, name, comment) VALUES (" . $fileId . ",'" . $name . "','" . $feedback . "')");
		}
		return false;
		
	}
	
	/*
	 * Gets query and execute
	 * @return true upon success 
	 */
	private function executeQuery($query)
	{
		$db =& JFactory::getDBO();
		$db->setQuery($query);
		
		if (!$result = $db->query())
		{
			//echo $db->stderr();	//test
			$res = false;
		}
		else
		{
			$res = true;
		}
		return $res;
	}
}